﻿using Dapper;
using SocketTool.Model.DataModels;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SocketTool.Dll.Base;
using io = System.IO;

namespace SocketTool.Dll
{
    public class ConnectionHistoryDll
    {
        private static readonly string dbFolderPath = io.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "SocketTool", "Db");
        private static readonly string dbPath = io.Path.Combine(dbFolderPath, "ConntionHistory.db3");
        private static readonly string connectionString = $"Data Source={dbPath}";

        private SQLiteConnection mSQLiteCon
        {
            get
            {
                return new SQLiteConnection(connectionString);
            }
        }

        /// <summary>
        /// 检查数据库文件
        /// </summary>
        /// <param name="checkError"></param>
        public bool CheckDbFile(out string checkError)
        {
            try
            {
                checkError = string.Empty;
                if (!io.Directory.Exists(dbFolderPath))
                    io.Directory.CreateDirectory(dbFolderPath);

                var dbFileInfo = new io.FileInfo(dbPath);
                #region 检查表字段
                //if (dbFileInfo.Exists && dbFileInfo.Length > 0)
                //{
                //    return CheckDBColmns(out errorMsg);
                //}
                #endregion

                if (dbFileInfo.Exists)
                    return true;

                #region 创建数据库文件
                SQLiteConnection.CreateFile(dbPath);
                #endregion

                using (var sqlCon = mSQLiteCon)
                {
                    if (sqlCon.State != System.Data.ConnectionState.Open)
                        sqlCon.Open();
                    #region 创建TCPClientHistory
                    sqlCon.Execute(@"CREATE TABLE `TCPClientHistory` (
                                `Id`    TEXT,
                                `Name`  TEXT,
                                `ServerIP`  TEXT,
                                `ServerPort`    INTEGER,
                                `SocketBufferSize`  INTEGER,
                                `MsgBufferSize` INTEGER,
                                `SendTimeout`   INTEGER,
                                `ReceiveTimeout`    INTEGER,
                                `Heartbeat` BOOLEAN,
                                `AutoRecvHeart` BOOLEAN,
                                `HeartbeatInterval` INTEGER,
                                `HeartbeatContent`  TEXT,
                                `Protocol`	BOOLEAN,
                                `ProtocolContent`   TEXT
                                `Slot`  INTEGER
                            ); ");
                    #endregion

                    #region 创建TCPServerHistory
                    sqlCon.Execute(@"CREATE TABLE `TCPServerHistory` (
                                `Id`    TEXT,
                                `Name`  TEXT,
                                `ListenPort`    INTEGER,
                                `Heartbeat` BOOLEAN,
                                `AutoRecvHeart` BOOLEAN,
                                `HeartbeatInterval` INTEGER,
                                `HeartbeatContent`  TEXT,
                                `SocketBufferSize`  INTEGER,
                                `MsgBufferSize` INTEGER,
                                `SendTimeout`   INTEGER,
                                `RecvTimeout`   INTEGER,
                                `Protocol`	BOOLEAN,
                                `ProtocolContent`   TEXT
                                `Slot`  INTEGER
                            ); ");
                    #endregion
                }
                return true;
            }
            catch (Exception ex)
            {
                checkError = "连接记录表异常";
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"检查连接记录表异常：{ex.Message}");
                Console.ResetColor();
                return false;
            }
        }

        /// <summary>
        /// 检查字段
        /// </summary>
        /// <param name="msg"></param>
        /// <returns></returns>
        private bool CheckDBColmns(out string msg)
        {
            msg = string.Empty;
            try
            {
                //using (var sqlCon = mSQLiteCon)
                //{
                //    var recentLinkerMessage_CreateSql = sqlCon.ExecuteScalar<string>("select sql from sqlite_master where type='table' and name ='RecentLinkerMessage'");
                //    if (!recentLinkerMessage_CreateSql.Contains("SenderHeadUrl"))
                //    {
                //        sqlCon.Execute("alter table RecentLinkerMessage add column SenderHeadUrl nvarchar;");
                //    }
                //}

                return true;
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"检查连接记录表文件字段异常 {ex.Message}");
                msg = $"检测最近会话数据库表文件字段异常";
                Console.ResetColor();
                return false;
            }
        }

        #region TCPClient
        /// <summary>
        /// 添加TCP客户端连接缓存
        /// </summary>
        /// <param name="client"></param>
        public void AddTCPClientHistory(TCPClientDataModel client)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if (sqlCon.State != System.Data.ConnectionState.Open)
                        sqlCon.Open();
                    var count = sqlCon.QueryFirst<int>("SELECT count(1) FROM TCPClientHistory WHERE Id = @Id", client);
                    if (count == 0)
                        sqlCon.Execute("INSERT INTO TCPClientHistory (Id,Name,ServerIP,ServerPort,SocketBufferSize,MsgBufferSize,SendTimeout,ReceiveTimeout,Heartbeat,AutoRecvHeart,HeartbeatInterval,HeartbeatContent,Protocol,ProtocolContent,Slot) VALUES (@Id,@Name,@ServerIP,@ServerPort,@SocketBufferSize,@MsgBufferSize,@SendTimeout,@ReceiveTimeout,@Heartbeat,@AutoRecvHeart,@HeartbeatInterval,@HeartbeatContent,@Protocol,@ProtocolContent,@Slot)", client);

                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"添加TCP客户端连接缓存 AddTCPClientHistory {ex.Message}");
                Console.ResetColor();
            }
        }

        /// <summary>
        /// 修改TCP客户端连接缓存
        /// </summary>
        /// <param name="client"></param>
        public void UpdateTCPClientHistory(TCPClientDataModel client)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if (sqlCon.State != System.Data.ConnectionState.Open)
                        sqlCon.Open();
                    sqlCon.Execute("UPDATE TCPClientHistory SET Name=@Name,SocketBufferSize=@SocketBufferSize,MsgBufferSize=@MsgBufferSize,SendTimeout=@SendTimeout,ReceiveTimeout=@ReceiveTimeout,Heartbeat=@Heartbeat,AutoRecvHeart=@AutoRecvHeart,HeartbeatInterval=@HeartbeatInterval,HeartbeatContent=@HeartbeatContent,Protocol=@Protocol,ProtocolContent=@ProtocolContent WHERE Id=@Id");
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"修改TCP客户端连接缓存 UpdateTCPClientHistory {ex.Message}");
                Console.ResetColor();
            }
        }

        /// <summary>
        /// 修改TCP客户端连接缓存排序序号
        /// </summary>
        /// <param name="id"></param>
        /// <param name="slot"></param>
        public void UpdateTCPClientHistorySlot(string id, int slot)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if (sqlCon.State != System.Data.ConnectionState.Open)
                        sqlCon.Open();
                    sqlCon.Execute("UPDATE TCPClientHistory SET Slot=@Slot WHERE Id=@Id", new { Slot = slot, Id = id });
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"修改TCP客户端连接缓存排序 UpdateTCPClientHistorySlot {ex.Message}");
                Console.ResetColor();
            }
        }

        /// <summary>
        /// 查询TCP客户端连接缓存信息
        /// </summary>
        /// <returns></returns>
        public List<TCPClientDataModel> QueryTCPClientHistoryList()
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if (sqlCon.State != System.Data.ConnectionState.Open)
                        sqlCon.Open();
                    return sqlCon.Query<TCPClientDataModel>("SELECT Id,Name,ServerIP,ServerPort,SocketBufferSize,MsgBufferSize,SendTimeout,ReceiveTimeout,Heartbeat,AutoRecvHeart,HeartbeatInterval,HeartbeatContent,Protocol,ProtocolContent,Slot FROM TCPClientHistory ORDER BY Slot DESC").ToList();
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"查询TCP客户端连接缓存信息 QueryTCPClientHistoryList {ex.Message}");
                Console.ResetColor();
                return null;
            }
        }

        /// <summary>
        /// 删除TCP客户端连接缓存
        /// </summary>
        /// <param name="Id"></param>
        public void DeleteTCPClientHistory(string Id)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if (sqlCon.State != System.Data.ConnectionState.Open)
                        sqlCon.Open();
                    using (var tran = sqlCon.BeginTransaction())
                    {
                        try
                        {
                            sqlCon.Execute("DELETE FROM TCPClientHistory WHERE Id=@Id", new { Id = Id },tran);
                            tran.Commit();
                        }
                        catch
                        {
                            tran.Rollback();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"删除TCP客户端连接缓存 DeleteTCPClientHistory {ex.Message}");
                Console.ResetColor();
            }
        }
        #endregion

        #region TCPServer
        /// <summary>
        /// 添加TCP服务端连接缓存
        /// </summary>
        /// <param name="server"></param>
        public void AddTCPServerHistory(TCPServerDataModel server)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if (sqlCon.State != System.Data.ConnectionState.Open)
                        sqlCon.Open();
                    var count = sqlCon.QueryFirst<int>("SELECT count(1) FROM TCPServerHistory WHERE Id = @Id", server);
                    if (count == 0)
                        sqlCon.Execute("INSERT INTO TCPServerHistory (Id,Name,ListenPort,Heartbeat,AutoRecvHeart,HeartbeatInterval,HeartbeatContent,SocketBufferSize,MsgBufferSize,SendTimeout,RecvTimeout,Protocol,ProtocolContent,Slot) VALUES (@Id,@Name,@ListenPort,@Heartbeat,@AutoRecvHeart,@HeartbeatInterval,@HeartbeatContent,@SocketBufferSize,@MsgBufferSize,@SendTimeout,@RecvTimeout,@Protocol,@ProtocolContent,@Slot)", server);

                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"添加TCP服务端连接缓存 AddTCPServerHistory {ex.Message}");
                Console.ResetColor();
            }
        }

        /// <summary>
        /// 修改TCP服务端连接缓存
        /// </summary>
        /// <param name="server"></param>
        public void UpdateTCPServerHistory(TCPServerDataModel server)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if (sqlCon.State != System.Data.ConnectionState.Open)
                        sqlCon.Open();
                    sqlCon.Execute("UPDATE TCPServerHistory SET Name=@Name,SocketBufferSize=@SocketBufferSize,MsgBufferSize=@MsgBufferSize,SendTimeout=@SendTimeout,RecvTimeout=@RecvTimeout,Heartbeat=@Heartbeat,AutoRecvHeart=@AutoRecvHeart,HeartbeatInterval=@HeartbeatInterval,HeartbeatContent=@HeartbeatContent,Protocol=@Protocol,ProtocolContent=@ProtocolContent WHERE Id=@Id",server);
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"修改TCP服务端连接缓存 UpdateTCPServerHistory {ex.Message}");
                Console.ResetColor();
            }
        }

        /// <summary>
        /// 修改TCP服务端连接缓存排序序号
        /// </summary>
        /// <param name="id"></param>
        /// <param name="slot"></param>
        public void UpdateTCPServerHistorySlot(string id,int slot)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if (sqlCon.State != System.Data.ConnectionState.Open)
                        sqlCon.Open();
                    sqlCon.Execute("UPDATE TCPServerHistory SET Slot=@Slot WHERE Id=@Id", new { Slot = slot, Id = id });
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"修改TCP服务端连接缓存排序 UpdateTCPServerHistorySlot {ex.Message}");
                Console.ResetColor();
            }
        }

        /// <summary>
        /// 查询TCP服务端连接缓存信息
        /// </summary>
        /// <returns></returns>
        public List<TCPServerDataModel> QueryTCPServerHistoryList()
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if (sqlCon.State != System.Data.ConnectionState.Open)
                        sqlCon.Open();
                    return sqlCon.Query<TCPServerDataModel>("SELECT Id,Name,ListenPort,Heartbeat,AutoRecvHeart,HeartbeatInterval,HeartbeatContent,SocketBufferSize,MsgBufferSize,SendTimeout,RecvTimeout,Protocol,ProtocolContent,Slot FROM TCPServerHistory ORDER BY Slot DESC").ToList();
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"查询TCP服务端连接缓存信息 QueryTCPServerHistoryList {ex.Message}");
                Console.ResetColor();
                return null;
            }
        }

        /// <summary>
        /// 删除TCP服务端连接缓存
        /// </summary>
        /// <param name="Id"></param>
        public void DeleteTCPServerHitory(string Id)
        {
            try
            {
                using (var sqlCon = this.mSQLiteCon)
                {
                    if (sqlCon.State != System.Data.ConnectionState.Open)
                        sqlCon.Open();
                    using (var tran = sqlCon.BeginTransaction())
                    {
                        try
                        {
                            sqlCon.Execute("DELETE FROM TCPServerHistory WHERE Id=@Id", new { Id = Id }, tran);
                            tran.Commit();
                        }
                        catch
                        {
                            tran.Rollback();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine($"删除TCP服务端连接缓存 DeleteTCPServerHitory {ex.Message}");
                Console.ResetColor();
            }
        }
        #endregion

    }
}
